﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProjetoBe.cs;
using System.Data.SqlClient;
using ProjetoUtil;
using System.Data;


namespace ProjetoDalc.cs
{
    public class ResponsavelDalc
    {
        public int inserirResponsavel(ResponsavelBe objeto)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                Util util = new Util();

                command.Connection = conexao;
                command.CommandText = "INSERT INTO RESPONSAVEL VALUES(@NOME, @NASCIMENTO, @PARENTESCO, @ENDERECO_ID, @DATA_CADASTRO, @CPF); SELECT SCOPE_IDENTITY();";
                command.Parameters.AddWithValue("NOME",objeto.nome);
                command.Parameters.AddWithValue("NASCIMENTO", objeto.nascimento);
                command.Parameters.AddWithValue("PARENTESCO", objeto.parentesco);
                command.Parameters.AddWithValue("ENDERECO_ID", objeto.endereco.id);
                command.Parameters.AddWithValue("DATA_CADASTRO", objeto.data_cadastro);
                command.Parameters.AddWithValue("CPF", util.stringSemCaracter(objeto.cpf));

                adapter.InsertCommand = command;
                conexao.Open();
                decimal id = (decimal)command.ExecuteScalar();
                objeto.id = Convert.ToInt32(id);
                conexao.Close();

                return objeto.id;
            }
        }

        public void inserirEndereco(EnderecoBe objeto)
        {
            using (SqlConnection conexao = Connection.CriarConexao()) 
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                Util util = new Util();

                command.Connection = conexao;
                command.CommandText = "INSERT INTO ENDERECO VALUES(@RUA, @NUMERO, @BAIRRO, @CEP, @COMPLEMENTO); SELECT SCOPE_IDENTITY();";
                command.Parameters.AddWithValue("RUA", objeto.rua);
                command.Parameters.AddWithValue("NUMERO", objeto.numero);
                command.Parameters.AddWithValue("BAIRRO", objeto.bairro);
                command.Parameters.AddWithValue("CEP", util.stringSemCaracter(objeto.cep));
                command.Parameters.AddWithValue("COMPLEMENTO", objeto.complemento);

                adapter.InsertCommand = command;

                conexao.Open();
                decimal id = (decimal)command.ExecuteScalar();
                objeto.id = Convert.ToInt32(id);
                conexao.Close();
                    
            }
        }

        public void inserirTelefone(TelefoneResponsavelBe objeto)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "INSERT INTO TELEFONE_RESPONSAVEL VALUES(@TELEFONE, @RESPONSAVEL_ID,@TELEFONE_TIPO);";
                command.Parameters.AddWithValue("TELEFONE", objeto.telefone);
                command.Parameters.AddWithValue("RESPONSAVEL_ID", objeto.responsavel.id);
                command.Parameters.AddWithValue("TELEFONE_TIPO", objeto.tipo);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                command.Clone();

            }
        }

        public List<ResponsavelBe> buscarResponsaveisCadastro(List<int> ids)
        {
            List<ResponsavelBe> listaResponsaveis = new List<ResponsavelBe>();

            if (ids.Count != 0) 
            {
                SqlDataReader reader;

                using (SqlConnection conexao = Connection.CriarConexao())
                {
                    SqlCommand command = new SqlCommand();
                    SqlDataAdapter adapter = new SqlDataAdapter();

                    command.Connection = conexao;
                    command.CommandText = "SELECT * FROM RESPONSAVEL WHERE RESPONSAVEL_ID IN(" + string.Join(",", ids) + ") ORDER BY RESPONSAVEL_ID DESC;";
                    conexao.Open();
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            ResponsavelBe novoResponsavel = new ResponsavelBe();
                            novoResponsavel.id = Convert.ToInt32(reader["RESPONSAVEL_ID"]);
                            novoResponsavel.nome = reader["RESPONSAVEL_NOME"].ToString();
                            novoResponsavel.nascimento = Convert.ToDateTime(reader["RESPONSAVEL_NASCIMENTO"]);
                            novoResponsavel.parentesco = reader["RESPONSAVEL_PARENTESCO"].ToString();
                            novoResponsavel.endereco.id = Convert.ToInt32(reader["ENDERECO_ID"]);
                            novoResponsavel.data_cadastro = Convert.ToDateTime(reader["RESPONSAVEL_DATA_CADASTRO"]);
                            novoResponsavel.cpf = reader["RESPONSAVEL_CPF"].ToString();
                            listaResponsaveis.Add(novoResponsavel);
                        }
                    }
                    conexao.Close();
                }
            }
            return listaResponsaveis;
        }

        public List<ResponsavelBe> buscarResponsaveisDesativadosPorCriancaID(int id_crianca)
        {
            SqlDataReader reader;
            List<ResponsavelBe> Lista = new List<ResponsavelBe>();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                
                command.Connection = conexao;
                command.CommandText = "SELECT DISTINCT R.* FROM DEPENDENCIA D INNER JOIN RESPONSAVEL R ON D.RESPONSAVEL_ID = R.RESPONSAVEL_ID AND D.DEPENDENCIA_FLAG = 'Desativo' WHERE D.CRIANCA_ID = @ID;";
                command.Parameters.AddWithValue("ID", id_crianca);
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ResponsavelBe novoResponsavel = new ResponsavelBe();
                        novoResponsavel.id = Convert.ToInt32(reader["RESPONSAVEL_ID"]);
                        novoResponsavel.nome = reader["RESPONSAVEL_NOME"].ToString();
                        novoResponsavel.nascimento = Convert.ToDateTime(reader["RESPONSAVEL_NASCIMENTO"]);
                        novoResponsavel.parentesco = reader["RESPONSAVEL_PARENTESCO"].ToString();
                        novoResponsavel.endereco.id = Convert.ToInt32(reader["ENDERECO_ID"]);
                        novoResponsavel.data_cadastro = Convert.ToDateTime(reader["RESPONSAVEL_DATA_CADASTRO"]);
                        novoResponsavel.cpf = reader["RESPONSAVEL_CPF"].ToString();
                        Lista.Add(novoResponsavel);
                    }
                }
                conexao.Close();
            }
            return Lista;
        }

        public List<ResponsavelBe> buscarResponsaveisAtivosPorCriancaID(int id_crianca)
        {
            SqlDataReader reader;
            List<ResponsavelBe> Lista = new List<ResponsavelBe>();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT DISTINCT R.* FROM DEPENDENCIA D INNER JOIN RESPONSAVEL R ON D.RESPONSAVEL_ID = R.RESPONSAVEL_ID AND D.DEPENDENCIA_FLAG = 'Ativo' WHERE D.CRIANCA_ID = @ID;";
                command.Parameters.AddWithValue("ID", id_crianca);
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ResponsavelBe novoResponsavel = new ResponsavelBe();
                        novoResponsavel.id = Convert.ToInt32(reader["RESPONSAVEL_ID"]);
                        novoResponsavel.nome = reader["RESPONSAVEL_NOME"].ToString();
                        novoResponsavel.nascimento = Convert.ToDateTime(reader["RESPONSAVEL_NASCIMENTO"]);
                        novoResponsavel.parentesco = reader["RESPONSAVEL_PARENTESCO"].ToString();
                        novoResponsavel.endereco.id = Convert.ToInt32(reader["ENDERECO_ID"]);
                        novoResponsavel.data_cadastro = Convert.ToDateTime(reader["RESPONSAVEL_DATA_CADASTRO"]);
                        novoResponsavel.cpf = reader["RESPONSAVEL_CPF"].ToString();
                        Lista.Add(novoResponsavel);
                    }
                }
                conexao.Close();
            }
            return Lista;
        }

        public void inserirTabelaTemporaria(ResponsavelBe responsavel)
        {
            List<ResponsavelBe> ListaVerifca = new List<ResponsavelBe>();
            ListaVerifca = retornarTabelaTemporaria();
            bool verifica = true;
         
            foreach (ResponsavelBe item in ListaVerifca)
            {
                if (item.nome == responsavel.nome && item.cpf == responsavel.cpf)
                    verifica = false;
            }

            if (verifica) 
            {
                using (SqlConnection conexao = Connection.CriarConexao())
                {
                    SqlCommand command = new SqlCommand();
                    SqlDataAdapter adpter = new SqlDataAdapter();
                    Util util = new Util();

                    command.Connection = conexao;
                    command.CommandText = "INSERT INTO RESPONSAVEL_TEMPORARIO VALUES(@ID, @NOME, @NASCIMENTO, @PARENTESCO, @ENDERECO_ID, @DATA_CADASTRO, @CPF); SELECT SCOPE_IDENTITY();";
                    command.Parameters.AddWithValue("ID", responsavel.id);
                    command.Parameters.AddWithValue("NOME", responsavel.nome);
                    command.Parameters.AddWithValue("NASCIMENTO", responsavel.nascimento);
                    command.Parameters.AddWithValue("PARENTESCO", responsavel.parentesco);
                    command.Parameters.AddWithValue("ENDERECO_ID", responsavel.endereco.id);
                    command.Parameters.AddWithValue("DATA_CADASTRO", responsavel.data_cadastro);
                    command.Parameters.AddWithValue("CPF", util.stringSemCaracter(responsavel.cpf));

                    adpter.InsertCommand = command;
                    conexao.Open();
                    command.ExecuteNonQuery();
                    conexao.Close();
                }
            
            }
            
        }

        public List<ResponsavelBe> retornarTabelaTemporaria() 
        { 
            SqlDataReader reader;
            List<ResponsavelBe> Lista = new List<ResponsavelBe>();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                
                command.Connection = conexao;
                command.CommandText = "SELECT* FROM RESPONSAVEL_TEMPORARIO;";
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ResponsavelBe novoResponsavel = new ResponsavelBe();
                        novoResponsavel.id = Convert.ToInt32(reader["RESPONSAVEL_ID"]);
                        novoResponsavel.nome = reader["RESPONSAVEL_NOME"].ToString();
                        novoResponsavel.nascimento = Convert.ToDateTime(reader["RESPONSAVEL_NASCIMENTO"]);
                        novoResponsavel.parentesco = reader["RESPONSAVEL_PARENTESCO"].ToString();
                        novoResponsavel.endereco.id = Convert.ToInt32(reader["ENDERECO_ID"]);
                        novoResponsavel.data_cadastro = Convert.ToDateTime(reader["RESPONSAVEL_DATA_CADASTRO"]);
                        novoResponsavel.cpf = reader["RESPONSAVEL_CPF"].ToString();
                        Lista.Add(novoResponsavel);
                    }
                }
                conexao.Close();
            }
            return Lista;
        }

        public void apagarTabelaTemporaria()
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "DELETE RESPONSAVEL_TEMPORARIO;";

                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public DataTable buscarResponsaveisPorIdentificacao(int identificacao)
        {
            SqlDataReader reader;
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adpter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "SELECT R.* FROM DEPENDENCIA D INNER JOIN RESPONSAVEL R ON D.RESPONSAVEL_ID = R.RESPONSAVEL_ID WHERE D.DEPENDENCIA_IDENTIFICACAO = @IDENTIFICACAO";
                command.Parameters.AddWithValue("@IDENTIFICACAO", identificacao);
                conexao.Open();
                reader = command.ExecuteReader();

                DataTable tabelaResponsaveis = new DataTable();
                tabelaResponsaveis.Load(reader);

                return tabelaResponsaveis;
            }
        }
    }
}
